# @Author ZhangGJ
# @Date 2021/12/23 06:15
import pymysql
from pymysql.cursors import DictCursor


def sharding(schema, schema_start, schema_end, table, table_start, table_end):
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456',
                           charset='utf8')
    cur = conn.cursor(DictCursor)

    for i in range(schema_start, schema_end + 1):
        if i < 10:
            schema_name = '{}{}{}'.format(schema, 0, i)
        else:
            schema_name = '{}{}'.format(schema, i)
        schema_query = f'create database {schema_name}'
        cur.execute(schema_query)
        cur.execute(f'use {schema_name}')
        print(f'Schema: {schema_name}============================================')
        for j in range(table_start, table_end + 1):
            table_name = '{}{}'.format(table, j)
            table_drop_query = f"DROP TABLE IF EXISTS {table_name};"
            table_query = f"CREATE TABLE {table_name} (" \
                          "`id` bigint NOT NULL AUTO_INCREMENT," \
                          "`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL," \
                          "`text` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL," \
                          "`create_time` datetime DEFAULT CURRENT_TIMESTAMP," \
                          "`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP," \
                          "PRIMARY KEY (`id`)" \
                          ") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;"

            cur.execute(table_drop_query)
            cur.execute(table_query)
            print(f'Table: {table_name}')
    cur.close()
    conn.commit()
    conn.close()


if __name__ == '__main__':
    # 参数
    # schema前缀
    # schema后面两位的起止数
    # table前缀
    # table后面两位的起止数
    sharding('test2022', 1, 2, 'test', 0, 3)
